USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetStandardIndicatorByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetStandardIndicatorByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetStandardIndicatorByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	

	SELECT Indicators.IndicatorId AS Id,
	       Companies.CompanyId, 
		   TemplateIndicators.IndicatorId AS TemplateId, 
		   TemplateIndicators.Number,
		   AnswerCategories.Code AS AnswerCategoryCode,
		   AnswerCategories.Text AS AnswerCategoryText,
		   AnswerCategories.Score AS AnswerCategoryScore

	FROM   [dbo].Companies AS Companies
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId
	INNER JOIN [dbo].IndicatorsInCompanies AS IndicatorsInCompanies ON Companies.CompanyId = IndicatorsInCompanies.CompanyId 
	INNER JOIN Indicators AS Indicators ON IndicatorsInCompanies.IndicatorId = Indicators.IndicatorId AND Companies.WorkingVersion - 1 = Indicators.Version 
	INNER JOIN [dbo].IndicatorTopics AS IndicatorTopics on IndicatorTopics.TopicId = Indicators.TopicId
	INNER JOIN [dbo].IndicatorThemes AS IndicatorThemes on IndicatorThemes.ThemeId = IndicatorTopics.ThemeId
	INNER JOIN [dbo].Indicators AS TemplateIndicators ON Indicators.Number = TemplateIndicators.Number
	LEFT OUTER JOIN [dbo].AnswerCategories AS AnswerCategories ON Indicators.RatedSelectedAnswer = AnswerCategories.AnswerCategoryId
	WHERE  (TemplateIndicators.Template = 1)  
	AND	   (Indicators.Template = 0)  
	AND	   (Companies.IsParked = 0)
	AND (IndicatorThemes.Number < 5)

END
GO


